﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace LibraryMIS.BLL
{
    public class LibraryService
    {
        private SqlConnection oleConnection1 = null;

        public LibraryService()
        {
            this.oleConnection1 = new SqlConnection(LibraryMIS.database.dbConnection.connection);
        }

        /// <summary>
        /// 添加身份
        /// </summary>
        /// <param name="textId"></param>
        /// <param name="comboDate"></param>
        /// <param name="comboNum"></param>
        public void AddOneIdentity(string textId, string comboDate, string comboNum)
        {
            oleConnection1.Open();
            string sql = "select * from identityinfo where identityname='" + textId + "'";
            SqlCommand cmd = new SqlCommand(sql, oleConnection1);
            if (null != cmd.ExecuteScalar())
                throw new Exception();

            sql = "insert into identityinfo values ('" + textId + "'," + comboDate + "," + comboNum + ")";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            oleConnection1.Close();
        }

        /// <summary>
        /// 返回读者类型
        /// </summary>
        /// <returns></returns>
        public DataSet getPerson()
        {
            oleConnection1.Open();
            string sql = "select identityname from identityinfo";
            SqlDataAdapter adp = new SqlDataAdapter(sql, oleConnection1);

            DataSet ds = new DataSet();
            adp.Fill(ds, "identi");
            oleConnection1.Close();
            return ds;
        }

        /// <summary>
        /// 添加读者信息
        /// </summary>
        /// <param name="textID"></param>
        /// <param name="textPN"></param>
        /// <param name="textName"></param>
        /// <param name="comboSex"></param>
        /// <param name="textPhone"></param>
        /// <param name="textCode"></param>
        /// <param name="textMoney"></param>
        /// <param name="comboId"></param>
        /// <param name="textRemark"></param>
        public void AddOnePerson(string textID, string textPN, string textName, string comboSex, string textPhone, string textCode, string textMoney, string comboId, string textRemark)
        {
            oleConnection1.Open();
            string sql = "select * from person where PID='" + textID + "' or PN='" + textPN + "'";
            SqlCommand cmd = new SqlCommand(sql, oleConnection1);
            if (null != cmd.ExecuteScalar())
                throw new Exception();
            sql = "insert into person values ('" + textID + "','" + textName + "','" + comboSex + "','" + textPhone + "','" + textPN + "','" + textCode + "','" + textMoney + "','" + comboId + "','" + textRemark + "',0)";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            oleConnection1.Close();
        }

        /// <summary>
        /// 获取所有读者的信息
        /// </summary>
        /// <returns></returns>
        public DataSet getPersontext()
        {
            oleConnection1.Open();
            string sql = "select PID as 借书证编号,PName as 姓名,PSex as 性别,PPhone as 电话,PN as 身份证,PCode as 密码," +
                "PMoney as 罚款,identityname as 身份,PRemark as 备注 from person";
            SqlDataAdapter adp = new SqlDataAdapter(sql, oleConnection1);
            DataSet ds1 = new DataSet();
            ds1.Clear();
            adp.Fill(ds1, "person");
            oleConnection1.Close();
            return ds1;
        }
    }
}
